<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
	<title>SQL Limitations | ElasticSearch 7.7 权威指南中文版</title>
	<meta name="keywords" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <meta name="description" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <!-- Give IE8 a fighting chance -->
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
	<link rel="stylesheet" type="text/css" href="../static/styles.css" />
	<script>
	var _link = 'sql-limitations.html';
    </script>
</head>
<body>
<div class="main-container">
    <section id="content">
        <div class="content-wrapper">
            <section id="guide" lang="zh_cn">
                <div class="container">
                    <div class="row">
                        <div class="col-xs-12 col-sm-8 col-md-8 guide-section">
                            <div style="color:gray; word-break: break-all; font-size:12px;">原英文版地址: <a href="https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-limitations.html" rel="nofollow" target="_blank">https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-limitations.html</a>, 原文档版权归 www.elastic.co 所有<br/>本地英文版地址: <a href="../en/sql-limitations.html" rel="nofollow" target="_blank">../en/sql-limitations.html</a></div>
                        <!-- start body -->
                  <div class="page_header">
<strong>重要</strong>: 此版本不会发布额外的bug修复或文档更新。最新信息请参考 <a href="https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html" rel="nofollow">当前版本文档</a>。
</div>
<div id="content">
<div class="breadcrumbs">
<span class="breadcrumb-link"><a href="index.html">Elasticsearch Guide [7.7]</a></span>
»
<span class="breadcrumb-link"><a href="xpack-sql.html">SQL access</a></span>
»
<span class="breadcrumb-node">SQL Limitations</span>
</div>
<div class="navheader">
<span class="prev">
<a href="sql-syntax-reserved.html">« Reserved keywords</a>
</span>
<span class="next">
<a href="search-aggregations.html">Aggregations »</a>
</span>
</div>
<div class="chapter xpack">
<div class="titlepage"><div><div>
<h2 class="title">
<a id="sql-limitations"></a>SQL Limitations<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a><a class="xpack_tag" href="https://www.elastic.co/subscriptions"></a>
</h2>
</div></div></div>
<h3>
<a id="large-parsing-trees"></a>Large queries may throw <code class="literal">ParsingExpection</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>Extremely large queries can consume too much memory during the parsing phase, in which case the Elasticsearch SQL engine will
abort parsing and throw an error. In such cases, consider reducing the query to a smaller size by potentially
simplifying it or splitting it into smaller queries.</p>
<h3>
<a id="sys-columns-describe-table-nested-fields"></a>Nested fields in <code class="literal">SYS COLUMNS</code> and <code class="literal">DESCRIBE TABLE</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>Elasticsearch has a special type of relationship fields called <code class="literal">nested</code> fields. In Elasticsearch SQL they can be used by referencing their inner
sub-fields. Even though <code class="literal">SYS COLUMNS</code> in non-driver mode (in the CLI and in REST calls) and <code class="literal">DESCRIBE TABLE</code> will still display
them as having the type <code class="literal">NESTED</code>, they cannot be used in a query. One can only reference its sub-fields in the form:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">[nested_field_name].[sub_field_name]</pre>
</div>
<p>For example:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT dep.dep_name.keyword FROM test_emp GROUP BY languages;</pre>
</div>
<h3>
<a id="_scalar_functions_on_nested_fields_are_not_allowed_in_where_and_order_by_clauses"></a>Scalar functions on nested fields are not allowed in <code class="literal">WHERE</code> and <code class="literal">ORDER BY</code> clauses<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>Elasticsearch SQL doesn’t support the usage of scalar functions on top of nested fields in <code class="literal">WHERE</code>
and <code class="literal">ORDER BY</code> clauses with the exception of comparison and logical operators.</p>
<p>For example:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM test_emp WHERE LENGTH(dep.dep_name.keyword) &gt; 5;</pre>
</div>
<p>and</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM test_emp ORDER BY YEAR(dep.start_date);</pre>
</div>
<p>are not supported but:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM test_emp WHERE dep.start_date &gt;= CAST('2020-01-01' AS DATE) OR dep.dep_end_date IS NULL;</pre>
</div>
<p>is supported.</p>
<h3>
<a id="_multi_nested_fields"></a>Multi-nested fields<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>Elasticsearch SQL doesn’t support multi-nested documents, so a query cannot reference more than one nested field in an index.
This applies to multi-level nested fields, but also multiple nested fields defined on the same level. For example, for this index:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">       column         |     type      |    mapping
----------------------+---------------+-------------
nested_A              |STRUCT         |NESTED
nested_A.nested_X     |STRUCT         |NESTED
nested_A.nested_X.text|VARCHAR        |KEYWORD
nested_A.text         |VARCHAR        |KEYWORD
nested_B              |STRUCT         |NESTED
nested_B.text         |VARCHAR        |KEYWORD</pre>
</div>
<p><code class="literal">nested_A</code> and <code class="literal">nested_B</code> cannot be used at the same time, nor <code class="literal">nested_A</code>/<code class="literal">nested_B</code> and <code class="literal">nested_A.nested_X</code> combination.
For such situations, Elasticsearch SQL will display an error message.</p>
<h3>
<a id="_paginating_nested_inner_hits"></a>Paginating nested inner hits<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>When SELECTing a nested field, pagination will not work as expected, Elasticsearch SQL will return <em>at least</em> the page size records.
This is because of the way nested queries work in Elasticsearch: the root nested field will be returned and it’s matching inner nested fields as well,
pagination taking place on the <span class="strong strong"><strong>root nested document and not on its inner hits</strong></span>.</p>
<h3>
<a id="normalized-keyword-fields"></a>Normalized <code class="literal">keyword</code> fields<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p><code class="literal">keyword</code> fields in Elasticsearch can be normalized by defining a <code class="literal">normalizer</code>. Such fields are not supported in Elasticsearch SQL.</p>
<h3>
<a id="_array_type_of_fields"></a>Array type of fields<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>Array fields are not supported due to the "invisible" way in which Elasticsearch handles an array of values: the mapping doesn’t indicate whether
a field is an array (has multiple values) or not, so without reading all the data, Elasticsearch SQL cannot know whether a field is a single or multi value.
When multiple values are returned for a field, by default, Elasticsearch SQL will throw an exception. However, it is possible to change this behavior through <code class="literal">field_multi_value_leniency</code> parameter in REST (disabled by default) or
<code class="literal">field.multi.value.leniency</code> in drivers (enabled by default).</p>
<h3>
<a id="_sorting_by_aggregation"></a>Sorting by aggregation<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>When doing aggregations (<code class="literal">GROUP BY</code>) Elasticsearch SQL relies on Elasticsearch’s <code class="literal">composite</code> aggregation for its support for paginating results.
However this type of aggregation does come with a limitation: sorting can only be applied on the key used for the aggregation’s buckets.
Elasticsearch SQL overcomes this limitation by doing client-side sorting however as a safety measure, allows only up to <span class="strong strong"><strong>512</strong></span> rows.</p>
<p>It is recommended to use <code class="literal">LIMIT</code> for queries that use sorting by aggregation, essentially indicating the top N results that are desired:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM test GROUP BY age ORDER BY COUNT(*) LIMIT 100;</pre>
</div>
<p>It is possible to run the same queries without a <code class="literal">LIMIT</code> however in that case if the maximum size (<span class="strong strong"><strong>10000</strong></span>) is passed,
an exception will be returned as Elasticsearch SQL is unable to track (and sort) all the results returned.</p>
<p>Moreover, the aggregation(s) used in the <code class="literal">ORDER BY</code> must be only plain aggregate functions. No scalar
functions or operators can be used, and therefore no complex columns that combine two ore more aggregate
functions can be used for ordering. Here are some examples of queries that are <span class="strong strong"><strong>not allowed</strong></span>:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT age, ROUND(AVG(salary)) AS avg FROM test GROUP BY age ORDER BY avg;

SELECT age, MAX(salary) - MIN(salary) AS diff FROM test GROUP BY age ORDER BY diff;</pre>
</div>
<h3>
<a id="_using_aggregation_functions_on_top_of_scalar_functions"></a>Using aggregation functions on top of scalar functions<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>Aggregation functions like <a class="xref" href="sql-functions-aggs.html#sql-functions-aggs-min" title="MIN"><code class="literal">MIN</code></a>, <a class="xref" href="sql-functions-aggs.html#sql-functions-aggs-max" title="MAX"><code class="literal">MAX</code></a>, etc. can only be used
directly on fields, and so queries like <code class="literal">SELECT MAX(abs(age)) FROM test</code> are not possible.</p>
<h3>
<a id="_using_a_sub_select"></a>Using a sub-select<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>Using sub-selects (<code class="literal">SELECT X FROM (SELECT Y)</code>) is <span class="strong strong"><strong>supported to a small degree</strong></span>: any sub-select that can be "flattened" into a single
<code class="literal">SELECT</code> is possible with Elasticsearch SQL. For example:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM (SELECT first_name, last_name FROM emp WHERE last_name NOT LIKE '%a%') WHERE first_name LIKE 'A%' ORDER BY 1;

  first_name   |   last_name
---------------+---------------
 Alejandro     |McAlpine
 Anneke        |Preusig
 Anoosh        |Peyn
 Arumugam      |Ossenbruggen</pre>
</div>
<p>The query above is possible because it is equivalent with:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT first_name, last_name FROM emp WHERE last_name NOT LIKE '%a%' AND first_name LIKE 'A%' ORDER BY 1;</pre>
</div>
<p>But, if the sub-select would include a <code class="literal">GROUP BY</code> or <code class="literal">HAVING</code> or the enclosing <code class="literal">SELECT</code> would be more complex than <code class="literal">SELECT X
FROM (SELECT ...) WHERE [simple_condition]</code>, this is currently <span class="strong strong"><strong>un-supported</strong></span>.</p>
<h3>
<a id="first-last-agg-functions-having-clause"></a>Using <a class="xref" href="sql-functions-aggs.html#sql-functions-aggs-first" title="FIRST/FIRST_VALUE"><code class="literal">FIRST</code></a>/<a class="xref" href="sql-functions-aggs.html#sql-functions-aggs-last" title="LAST/LAST_VALUE"><code class="literal">LAST</code></a> aggregation functions in <code class="literal">HAVING</code> clause<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>Using <code class="literal">FIRST</code> and <code class="literal">LAST</code> in the <code class="literal">HAVING</code> clause is not supported. The same applies to
<a class="xref" href="sql-functions-aggs.html#sql-functions-aggs-min" title="MIN"><code class="literal">MIN</code></a> and <a class="xref" href="sql-functions-aggs.html#sql-functions-aggs-max" title="MAX"><code class="literal">MAX</code></a> when their target column
is of type <a class="xref" href="keyword.html" title="Keyword datatype"><code class="literal">keyword</code></a> as they are internally translated to <code class="literal">FIRST</code> and <code class="literal">LAST</code>.</p>
<h3>
<a id="group-by-time"></a>Using TIME data type in GROUP BY or <a class="xref" href="sql-functions-grouping.html#sql-functions-grouping-histogram" title="HISTOGRAM"><code class="literal">HISTOGRAM</code></a><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>Using <code class="literal">TIME</code> data type as a grouping key is currently not supported. For example:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT count(*) FROM test GROUP BY CAST(date_created AS TIME);</pre>
</div>
<p>On the other hand, it can still be used if it’s wrapped with a scalar function that returns another data type,
for example:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT count(*) FROM test GROUP BY MINUTE((CAST(date_created AS TIME));</pre>
</div>
<p><code class="literal">TIME</code> data type is also currently not supported in histogram grouping function. For example:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT HISTOGRAM(CAST(birth_date AS TIME), INTERVAL '10' MINUTES) as h, COUNT(*) FROM t GROUP BY h</pre>
</div>
<h3>
<a id="geo-sql-limitations"></a>Geo-related functions<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>Since <code class="literal">geo_shape</code> fields don’t have doc values these fields cannot be used for filtering, grouping or sorting.</p>
<p>By default,<code class="literal">geo_points</code> fields are indexed and have doc values. However only latitude and longitude are stored and
indexed with some loss of precision from the original values (4.190951585769653E-8 for the latitude and
8.381903171539307E-8 for longitude). The altitude component is accepted but not stored in doc values nor indexed.
Therefore calling <code class="literal">ST_Z</code> function in the filtering, grouping or sorting will return <code class="literal">null</code>.</p>
<h3>
<a id="fields-from-source"></a>Retrieving from <code class="literal">_source</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>Most of Elasticsearch SQL’s columns are retrieved from the document’s <code class="literal">_source</code> and there is no attempt to get the columns content from
<code class="literal">docvalue_fields</code> not even in the case <a class="xref" href="mapping-source-field.html" title="_source field"><code class="literal">_source</code></a> field is disabled in the mapping explicitly.
If a column, for which there is no source stored, is asked for in a query, Elasticsearch SQL will not return it. Field types that don’t follow
this restriction are: <code class="literal">keyword</code>, <code class="literal">date</code>, <code class="literal">scaled_float</code>, <code class="literal">geo_point</code>, <code class="literal">geo_shape</code> since they are NOT returned from <code class="literal">_source</code> but
from <code class="literal">docvalue_fields</code>.</p>
<h3>
<a id="fields-from-docvalues"></a>Retrieving from <code class="literal">docvalue_fields</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>When the number of columns retrievable from <code class="literal">docvalue_fields</code> is greater than the configured <a class="xref" href="index-modules.html#dynamic-index-settings" title="Dynamic index settings"><code class="literal">index.max_docvalue_fields_search</code> setting</a>
the query will fail with <code class="literal">IllegalArgumentException: Trying to retrieve too many docvalue_fields</code> error. Either the mentioned Elasticsearch
setting needs to be adjusted or fewer columns retrievable from <code class="literal">docvalue_fields</code> need to be selected.</p>
<h3>
<a id="aggs-in-pivot"></a>Aggregations in the <a class="xref" href="sql-syntax-select.html#sql-syntax-pivot" title="PIVOT"><code class="literal">PIVOT</code></a> clause<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>The aggregation expression in <a class="xref" href="sql-syntax-select.html#sql-syntax-pivot" title="PIVOT"><code class="literal">PIVOT</code></a> will currently accept only one aggregation. It is thus not possible to obtain multiple aggregations for any one pivoted column.</p>
<h3>
<a id="subquery-in-pivot"></a>Using a subquery in <a class="xref" href="sql-syntax-select.html#sql-syntax-pivot" title="PIVOT"><code class="literal">PIVOT</code></a>'s <code class="literal">IN</code>-subclause<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/limitations.asciidoc">edit</a>
</h3>
<p>The values that the <a class="xref" href="sql-syntax-select.html#sql-syntax-pivot" title="PIVOT"><code class="literal">PIVOT</code></a> query could pivot must be provided in the query as a list of literals; providing a subquery instead to build this list is not currently supported. For example, in this query:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM test_emp PIVOT (SUM(salary) FOR languages IN (1, 2))</pre>
</div>
<p>the <code class="literal">languages</code> of interest must be listed explicitly: <code class="literal">IN (1, 2)</code>. On the other hand, this example would <span class="strong strong"><strong>not work</strong></span>:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM test_emp PIVOT (SUM(salary) FOR languages IN (SELECT languages FROM test_emp WHERE languages &lt;=2 GROUP BY languages))</pre>
</div>
</div>
<div class="navfooter">
<span class="prev">
<a href="sql-syntax-reserved.html">« Reserved keywords</a>
</span>
<span class="next">
<a href="search-aggregations.html">Aggregations »</a>
</span>
</div>
</div>

                  <!-- end body -->
                        </div>
                        <div class="col-xs-12 col-sm-4 col-md-4" id="right_col">
                        
                        </div>
                    </div>
                </div>
            </section>
        </div>
    </section>
</div>
<script src="../static/cn.js"></script>
</body>
</html>